﻿
--GO--
/** 注意 GO 的书写方式，能够让BP识别 */

--GO--

CREATE PROCEDURE [dbo].[SYST_pGetScopeRows] 

  @Sql nvarchar(2000),

  @Top int,

  @Order nvarchar(500),

  @nFrom int,

  @nTo int

AS

BEGIN

  declare @NewSql nvarchar(3000),@NewSqlStat nvarchar(3000),@TopStr nvarchar(100);

  set @NewSql=@Sql;

  set @TopStr='';

  set @NewSql=ltrim(rtrim(@NewSql));

  if(@Top>0)set @TopStr=' Top '+Cast(@Top as varchar)+' ';

  if (lower(left(@NewSql,6))='select' and charindex('order by',lower(@NewSql))=0)

  Begin

     set @NewSql='select '+@TopStr+' row_number() over ('+@Order+') as RowNum,A.* from ('+@NewSql+') as A';

     set @NewSql='select * from ('+@NewSql+') as T where T.RowNum between '+Cast(@nFrom as varchar)+' and '+Cast(@nTo as varchar)+' '+@Order+';';

     exec (@NewSql);
  End

  else
     select 'Not_Select_Sql_statement!'

END

--GO--

CREATE PROCEDURE [dbo].[sch1]
   @TableName varchar(50),
   @format varchar(200),
   @splitter varchar(10)
AS
BEGIN
     declare @return varchar(2000);set @return='';
     declare @name varchar(50);
	 declare cursor1 cursor for         --定义游标cursor1
		select name from dbo.syscolumns where id=(select id from sysobjects where name=@TableName);             --使用游标的对象(跟据需要填入select文)
	 open cursor1                       --打开游标
	 fetch next from cursor1 into @name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
	 while @@fetch_status=0              --判断是否成功获取数据
	 begin
	   if(@return<>'')set @return=@return+@splitter;
	   set @return=@return+REPLACE(@format,'@',@name);
	   fetch next from cursor1 into @name  --将游标向下移1行
	 end
	 close cursor1                       --关闭游标
	 deallocate cursor1 
	 select @return;
END

 --GO--

CREATE PROCEDURE [dbo].[sch]
   @TableName varchar(50),
   @Cols      int
AS
BEGIN
   if(@Cols<=0)return;
   
   declare @col nvarchar(500);   
   set @col='';
   if(@Cols & 1 = 1) set @col=@col+',name';
   if(@Cols & 2 = 2) set @col=@col+',''ui_''+name as UI变量';
   if(@Cols & 4 = 4) set @col=@col+',''<asp:TextBox ID="ui_''+name+''" runat="server" Width="97%"></asp:TextBox>'' as UI控件';
   if(@Cols & 8 = 8) set @col=@col+',''String ''+name+'' = this.ui_''+name+''.Text;'' as 获取变量';
   if(@Cols & 16 = 16) set @col=@col+','''+@TableName+'''+''.''+name+'' = ''+name+'';'' as 对象属性赋值';
   if(@Cols & 32 = 32) set @col=@col+',''this.ui_''+name+''.Text = ''+'''+@TableName+'''+''.''+name+'';'' as 控件赋值';
 

   declare @sql nvarchar(2000);
   set @sql='select '+RIGHT(@col,LEN(@col)-1);
   set @sql=@sql+' from syscolumns ';
   set @sql=@sql+' where id=(select id from sysobjects where name='''+@TableName+''');';
   exec(@sql);
END

  --GO--

CREATE FUNCTION [dbo].[f_GetDaysTable]
(
   @BeginDay Smalldatetime,
   @EndDay SmallDateTime
)
RETURNS 
@DaysTable TABLE 
(
    [Day] char(10)
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	declare @d smalldatetime;
	set @d=@BeginDay;
	while(DATEDIFF(D,@d,@EndDay)>=0) 
	Begin
	   INSERT @DaysTable ([Day]) Values(Convert(char(10),@d,120));
	   set @d=DATEADD(d,1,@d);
	End
	RETURN;
End

  --GO--

CREATE PROCEDURE [dbo].[SYST_pGetSingleRow] 

  @Sql nvarchar(2000),

  @Top int,

  @Order nvarchar(500),

  @nPos int

AS

BEGIN

  exec dbo.SYST_pGetScopeRows @Sql,@Top,@Order,@nPos,@nPos;

END

 --GO--

CREATE PROCEDURE [dbo].[SYST_pGetPageRows] 


  @Sql nvarchar(2000),

  @Top int,

  @Order nvarchar(500),

  @nPageSize int,

  @nPageID int

AS

BEGIN

  declare @nFrom int,@nTo int;

  set @nFrom=(@nPageID-1)*@nPageSize+1;

  set @nTo=@nPageID*@nPageSize;

  exec dbo.SYST_pGetScopeRows @Sql,@Top,@Order,@nFrom,@nTo;

END